﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using Wicresoft.UnifyShow.Business;
using Wicresoft.UnifyShow.Business.Models;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class DutyPlan : Basic, IDutyPlan
    {
        private const string sqlDelete = "delete from dutyplan where id=:id";
        private const string sqlDeleteByDay = "delete from dutyplan where dutyday=:day and SITEID=:SITEID";
        private const string sqlGetAll = "select * from dutyplan";
        private const string sqlDeleteLog = "delete from dutylog where id=:id";

        public List<DutyPlanModel> GetDutyPlanByTime(DateTime start, DateTime end, decimal LocationID)
        {
            List<DutyPlanModel> list = new List<DutyPlanModel>();
            QueryParameter[] attParam = new QueryParameter[] {
                        new QueryParameter(){ OracleDbColumnName="DutyDay", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=start,QueryOperator=QueryOperatorType.bigger,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="DutyDay", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=end,QueryOperator=QueryOperatorType.smaller,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="SITEID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=LocationID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            list = ModelHelper<DutyPlanModel>.GetModel_By_QueryParams(attParam, null, null, null, oda);

            return list;

        }

        public DutyPlanModel GetDutyPlanByDutyDay(DateTime dutyDay, decimal LocationID)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="DUTYDAY", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=dutyDay,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                        new QueryParameter(){ OracleDbColumnName="SITEID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=LocationID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };
            return ModelHelper<DutyPlanModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public DutyPlanModel GetDutyPlanByID(int id)
        {
            QueryParameter[] attParam = new QueryParameter[] {
                    new QueryParameter(){ OracleDbColumnName="ID", OracleDbColumnType=OracleDbType.Decimal,
                        OracleDbColumnValue=id,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where},
                    };
            return ModelHelper<DutyPlanModel>.GetModel_By_QueryParams(attParam, null, null, null, oda).FirstOrDefault();
        }

        public int Add(DutyPlanModel model)
        {
            var id = ModelHelper<DutyPlanModel>.AddModel_By_QueryParams_Return_ID(model, new List<string>() { "ID" }, oda);

            //添加值班记录
            if (id != 0)
            {
                var log = new DutyLog()
                {
                    CreateTime = DateTime.Now,
                    DailyRecord = "",
                    PlanID = id,
                    SITEID = model.SITEID,
                    Submitter = model.Submitter,
                    DutyDay = model.DutyDay
                };

                var row = InsertDutyLog(log);
            }

            return 1;
        }

        public void Delete(decimal id)
        {
            ModelHelper<DutyPlanModel>.UpdateOrDeleteModel_By_DirectSql(sqlDelete, new List<OracleParameter>(){
                     new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=id},
                 }, oda);

            //删除值班记录
            var log = GetDutyLogByPlanID(id);
            if (log != null)
            {
                DeleteDutyLog(log.ID);
            }

        }

        public bool DeleteByDutyDay(DateTime dutyDay, decimal LocationID)
        {
            var dbModel = GetDutyPlanByDutyDay(dutyDay, LocationID);

            var rows = ModelHelper<DutyPlanModel>.UpdateOrDeleteModel_By_DirectSql(sqlDeleteByDay,
                new List<OracleParameter>(){
                     new OracleParameter(){ ParameterName="day",OracleDbType=OracleDbType.Date,Value=dutyDay},
                     new OracleParameter(){ ParameterName="SITEID",OracleDbType=OracleDbType.Decimal,Value=LocationID},
                 }, oda);

            //删除值班记录
            var log = GetDutyLogByPlanID(dbModel.ID);
            if (log != null)
            {
                DeleteDutyLog(log.ID);
            }

            return rows > 0;
        }

        public bool DeleteList(IList<DutyPlanModel> models)
        {
            var res = true;
            foreach (var item in models)
            {
                res = DeleteByDutyDay(item.DutyDay, item.SITEID);
                if (!res)
                    break;
            }
            return res;
        }

        public int Update(DutyPlanModel model)
        {
            var dbModel = GetDutyPlanByDutyDay(model.DutyDay, model.SITEID);

            if (dbModel != null)
            {
                int res = ModelHelper<DutyPlanModel>.UpdateModel_By_QueryParams(new List<QueryParameter>(){
                new QueryParameter(){OracleDbColumnName="ID",OracleDbColumnValue=dbModel.ID,OracleDbColumnType=OracleDbType.Decimal,
                    PredicateType=PredicateType.where,QueryOperator=QueryOperatorType.equal}
                }, model, new List<string>() { "ID" }, oda);

                return res;
            }
            return 0;
        }

        public List<DutyPlanModel> GetAllDutyPlan(decimal LocationID)
        {
            var all = ModelHelper<DutyPlanModel>.GetModel_By_DirectSql(sqlGetAll, null, oda);

            return all.Where(x => x.SITEID == LocationID).ToList();
        }

        public bool IsExist(DateTime dutyday, decimal LocationID)
        {
            var plist = GetAllDutyPlan(LocationID);
            bool reslut = false;
            DateTime n = new DateTime();

            if (plist.Count > 0)
            {
                foreach (var item in plist)
                {
                    n = item.DutyDay;
                    if (n == dutyday)
                    {
                        reslut = true;
                        break;
                    }
                }
            }
            return reslut;
        }

        public DutyPlanModel GetTodayDutyInfo(decimal LocationID)
        {
            var plist = GetAllDutyPlan(LocationID);
            string n = null;
            string nowtime = DateTime.Now.ToShortDateString();
            DutyPlanModel model = new DutyPlanModel();

            if (plist.Count > 0)
            {
                foreach (var item in plist)
                {
                    n = item.DutyDay.ToShortDateString();
                    if (n == nowtime)
                    {
                        model = item;
                        break;
                    }
                }
            }
            return model;
        }

        public int AddList(IList<DutyPlanModel> models)
        {
            var row = 0;

            foreach (var item in models)
            {
                row = Add(item);
            }

            return row;
        }

        public int UpdateList(IList<DutyPlanModel> models)
        {
            int res = 0;
            foreach (var item in models)
            {
                res = Update(item);
                if (res <= 0)
                    break;
            }
            return res;
        }

        #region 值班记录

        public List<DutyLog> QueryDutyLog(DateTime startDate, DateTime endDate, decimal LocationID, out decimal totalCount, int page, int rows, string sort = "ID", string order = "Descending")
        {
            OrderbyParameter op = SQLHelper.GetOrderbyParameterByColumnNameAndSort(sort, order);

            List<QueryParameter> attParam = new List<QueryParameter>() { 
                new QueryParameter(){OracleDbColumnName="DutyDay", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=startDate,QueryOperator=QueryOperatorType.bigger,PredicateType=PredicateType.where},

                 new QueryParameter(){OracleDbColumnName="DutyDay", OracleDbColumnType=OracleDbType.Date,
                        OracleDbColumnValue=endDate,QueryOperator=QueryOperatorType.smaller,PredicateType=PredicateType.where},
          
                            new QueryParameter(){OracleDbColumnName="SITEID", OracleDbColumnType=OracleDbType.Decimal,
                       OracleDbColumnValue=LocationID,QueryOperator=QueryOperatorType.equal,PredicateType=PredicateType.where}
                    };

            totalCount = ModelHelper<DutyLog>.GetModelCount_By_QueryParams(attParam, oda);
            return ModelHelper<DutyLog>.GetModel_By_QueryParams(attParam, new List<OrderbyParameter>() { op }, rows, page, oda);
        }

        public DutyLog GetDutyLogByPlanID(decimal planID)
        {
            var param = new List<QueryParameter>(){
                new QueryParameter(){
                OracleDbColumnName = "PlanID",OracleDbColumnType = OracleDbType.Decimal,
                OracleDbColumnValue = planID,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                }
            };
            return ModelHelper<DutyLog>.GetModel_By_QueryParams(param, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        public bool InsertDutyLog(DutyLog model)
        {
            var log = GetDutyLogDutyDay(model.DutyDay, model.SITEID);
            if (log == null)
            {
                var rows = ModelHelper<DutyLog>.AddModel_By_QueryParams(model, new List<string>() { "ID", "LocationName", "WatchKeeper", "DutyPhone" }, oda);
                return rows == 1;
            }
            else
            {
                var param = new List<QueryParameter>(){
                    new QueryParameter(){
                    OracleDbColumnName = "ID",OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = log.ID,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                    }
                };
                var row = ModelHelper<DutyLog>.UpdateModel_By_QueryParams(param, model, new List<string>() { "ID", "LocationName", "WatchKeeper", "DutyPhone" }, oda);
                return row == 1;
            }

        }

        public int WriteDutyLogByList(IList<DutyLog> models)
        {
            return ModelHelper<DutyLog>.AddListModel_By_QueryParams(models, new List<string>() { "ID" }, oda);
        }

        public bool DeleteDutyLog(decimal id)
        {
            var row = ModelHelper<DutyPlanModel>.UpdateOrDeleteModel_By_DirectSql(sqlDeleteLog, new List<OracleParameter>(){
                     new OracleParameter(){ ParameterName="id",OracleDbType=OracleDbType.Decimal,Value=id},
                 }, oda);
            return row == 1;
        }

        public DutyLog GetDutyLogDutyDay(DateTime dutyDay, decimal LocationID)
        {
            var param = new List<QueryParameter>(){
                new QueryParameter(){
                    OracleDbColumnName = "DutyDay",OracleDbColumnType = OracleDbType.Date,
                    OracleDbColumnValue = dutyDay,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                },
                new QueryParameter(){
                    OracleDbColumnName = "SITEID",OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = LocationID,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                },
            };

            return ModelHelper<DutyLog>.GetModel_By_QueryParams(param, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }

        public DutyLog GetDutyLogByID(decimal ID)
        {
            var param = new List<QueryParameter>(){
                new QueryParameter(){
                    OracleDbColumnName = "ID",OracleDbColumnType = OracleDbType.Decimal,
                    OracleDbColumnValue = ID,PredicateType = PredicateType.where,QueryOperator = QueryOperatorType.equal
                }
            };

            return ModelHelper<DutyLog>.GetModel_By_QueryParams(param, new List<OrderbyParameter>() { }, null, null, oda).FirstOrDefault();
        }


        #endregion

        private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction trans, CommandType cmdType, string cmdText, IList<OracleParameter> commandParameters)
        {
            if (connection.State != ConnectionState.Open) connection.Open();
            command.Connection = connection;
            command.CommandText = cmdText;
            command.CommandType = cmdType;
            command.BindByName = true;

            if (trans != null) command.Transaction = trans;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                {
                    if (parm != null)
                    {
                        if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
                        {
                            parm.Value = DBNull.Value;
                        }
                        var value = parm.Value;
                        if (value.GetType() == typeof(string) || parm.OracleDbType == OracleDbType.NVarchar2)
                        {
                            if (value != DBNull.Value)
                            {
                                parm.Size = ((string)value).Length > 4000 ? -1 : 4000;
                            }
                            else
                            {
                                parm.Size = 0;
                            }
                        }
                        command.Parameters.Add(parm);
                    }
                }
            }
        }



    }
}
